package com.lhl.dao;

import com.lhl.entity.Student;
import com.lhl.util.ConnUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    @Override
    public List<Student> queryAllStudents() {
        //获取工具类中的数据库连接
        Connection conn = ConnUtils.getConnection();
        //执行SQL语句的对象
        PreparedStatement ps = null;
        //接受执行完返回的数据集
        ResultSet rs = null;
        //创建一个学生集合
        List<Student> students = new ArrayList<>();
        //SQL语句
        String sql = "select * from student";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                //数据每读取一行就创建一个新学生
                Student student = new Student();
                //把获取到的数据存放到新建的学生信息中
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                student.setBirthday(rs.getDate("birthday"));
                //把存好信息的学生添加到集合中
                students.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            ConnUtils.close(rs, ps, conn);
        }
        //把查询到的所有信息返回
        return students;
    }

    @Override
    public Student queryStudent(int id) {
        //获取工具类中的数据库连接
        Connection conn = ConnUtils.getConnection();
        //执行SQL语句的对象
        PreparedStatement ps = null;
        //接受执行完返回的数据集
        ResultSet rs = null;
        //新建一个学生来存放数据
        Student student = new Student();
        //SQL语句
        String sql = "select * from student where id=?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                //把获取到的数据存放到新建的学生信息中
                student.setId(rs.getInt("id"));
                student.setName(rs.getString("name"));
                student.setSex(rs.getString("sex"));
                student.setAge(rs.getInt("age"));
                student.setBirthday(rs.getDate("birthday"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            ConnUtils.close(rs, ps, conn);
        }
        //把查询到的信息返回
        return student;
    }

    @Override
    public int insertStudent(Student student) {
        //获取工具类中的数据库连接
        Connection conn = ConnUtils.getConnection();
        //执行SQL语句的对象
        PreparedStatement ps = null;
        //SQL语句
        String sql = "insert into student(name,sex,age,birthday)\n" +
                "values(?,?,?,?);";
        //数据库中受影响的行数
        int count = 0;
        try {
            ps = conn.prepareStatement(sql);
            //把要添加的学生信息按照SQL语句中的问号一一对应
            ps.setString(1, student.getName());
            ps.setString(2, student.getSex());
            ps.setInt(3, student.getAge());
            //数据库中的date是java.sql.Date包下的
            //实体类中的date是java.util.Date包下的
            //需要转换类型
            Date date = new Date(student.getBirthday().getTime());
            ps.setDate(4, date);
            count = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            ConnUtils.close(ps, conn);
        }
        //返回数据库中受影响的行数
        return count;
    }

    @Override
    public int updateStudent(Student student) {
        //获取工具类中的数据库连接
        Connection conn = ConnUtils.getConnection();
        //执行SQL语句的对象
        PreparedStatement ps = null;
        //SQL语句
        String sql = "update student set name=?,sex=?,age=?,birthday=? where id=?";
        //数据库中受影响的行数
        int count = 0;
        try {
            ps = conn.prepareStatement(sql);
            //把要添加的学生信息按照SQL语句中的问号一一对应
            ps.setString(1, student.getName());
            ps.setString(2, student.getSex());
            ps.setInt(3, student.getAge());
            //数据库中的date是java.sql.Date包下的
            //实体类中的date是java.util.Date包下的
            //需要转换类型
            Date date = new Date(student.getBirthday().getTime());
            ps.setDate(4, date);
            ps.setInt(5, student.getId());
            count = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            ConnUtils.close(ps, conn);
        }
        //返回数据库中受影响的行数
        return count;
    }

    @Override
    public int deleteStudent(int id) {
        //获取工具类中的数据库连接
        Connection conn = ConnUtils.getConnection();
        //执行SQL语句的对象
        PreparedStatement ps = null;
        //SQL语句
        String sql = "delete from student where id=?";
        //数据库中受影响的行数
        int count = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            count = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            //关闭资源
            ConnUtils.close(ps, conn);
        }
        //返回数据库中受影响的行数
        return count;
    }
}
